Bind Java Bean to ZK Spreadsheet

This article is out of date, please refer to http://books.zkoss.org/wiki/ZK_Spreadsheet_Essentials for more up to date information.

Available in ZK Spreadsheet EE only

ZK Spreadsheet can resolve the name expressions in cells to bind the data from the back end Java beans automatically.

Purpose

Data binding spreadsheet cells to back end Java beans.

Template Excel File with Proper Name Expressions

Construct an Excel template file with proper name expressions in cells.

You will see #NAME? in cells because Excel does not know such names. However, they will be interpreted when import into ZK Spreadsheet component.

How ZK Spreadsheet Resolve my Java Beans

For variables in cells, if valid Excel Defined Names found in template file, ZK Spreadsheet will use them. If not, ZK Spreadsheet follows ZK's EL expression variable resolving mechanism. It first tries to find any matching zscript variables defined in ZUML page. Then check ids of ZK fellow components. Then search in ZK components' attribute map. Finally ask variable resolvers defined in the zul page to retrieve the bean of the named variable. If still none is found, it will return #NAME? as Excel's original behavior. Once variables are resolved, the associated getter are called and value returned in the cell.

ZUML

In ZUML page, we declare a zscript variable "dataBean" so ZK Spreadsheet shall find it in no time.

<zk>
	<zscript>
	<![CDATA[
		import org.zkoss.zssessentials.bean.java.DataBean;
		DataBean dataBean = new DataBean();
		// initialize dataBean attributes ...
		dataBean.setLiquidAssets(146504221);
		...
	]]>
	</zscript>
	<spreadsheet id="bsheet" 
		src="/WEB-INF/excel/bean/bsheet.xls"
		maxrows="200" 
		maxcolumns="40" 
		vflex="1" 
		width="100%">
	</spreadsheet>
</zk>

Java Bean

This is the Java Bean in our example. Mainly getters and setters for various attributes for a balance sheet.

package org.zkoss.zssessentials.bean.java;
public class DataBean {
	private double liquidAssets;
	private double fundInvestment;
	private double fixedAssets;
	private double intangibleAsset; 
	private double otherAssets;
	private double currentLiabilities;
	private double longTermLiabilities;
	private double otherLiabilities;
	private double capitalStock;
	private double capitalSurplus;
	private double retainedEarnings;
	private double otherEquity;
	private double treasuryStock;
	
	public double getLiquidAssets() {
		return liquidAssets;
	}

	public void setLiquidAssets(double liquidAssets) {
		this.liquidAssets = liquidAssets;
	}

	//...other getters and setters
}

Result

When Value of Java Bean Changes

Will ZK Spreadsheet update the cells if the value of the Java bean changes? Well, if you notify it.

	bsheet.getBook().notifyChange(new String[] {"dataBean"});

When you notify the ZK Spreadsheet work book model that beans with the specified names have changed, it will collect which cells are affected (i.e. those dependent cells with the specified bean names), and publish data change event accordingly. The ZK Spreadsheet UI or any other components that have subscribed to the work book's event queue and are interested in the event will then retrieve the new cells' data from the beans and update themselves.

View complete source code of ZUML bsheet.zul

Version History

Version	Date	Content

All source code listed in this book is at Github.